package com.ymtms.statisticalform.dao;

import com.ymtms.statisticalform.pojo.CollectingMoneyBalance;
import com.ymtms.statisticalform.pojo.GoodsbillMaster;
import com.ymtms.statisticalform.pojo.MoneyFreight;
import com.ymtms.statisticalform.util.TimeRangeUtil;
import com.ymtms.statisticalform.vo.GridRequest;
import com.ymtms.statisticalform.vo.GridResult;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface GoodsbillMasterMapper {
    List<GoodsbillMaster> queryByPage(@Param("factDealDateString") String goodsbillMaster,
                                             @Param("balanceDateRange") TimeRangeUtil balanceDateRange);
    int deleteByPrimaryKey(Integer gmid);

    int insert(GoodsbillMaster record);

    int insertSelective(GoodsbillMaster record);

    GoodsbillMaster selectByPrimaryKey(Integer gmid);

    int updateByPrimaryKeySelective(GoodsbillMaster record);

    int updateByPrimaryKey(GoodsbillMaster record);

    @Select("<script>" +
            "SELECT send_goods_customer AS name,sum(carriage) AS carriage,sum(reduce_fund) AS reduce_fund,sum(pay_kickback) AS pay_kickback," +
            "sum(money_of_change_pay) AS money_of_change_pay,sum(carry_goods_fee) AS carry_goods_fee,sum(insurance) AS insurance " +
            "FROM goodsbill_master WHERE flag=0 AND fact_deal_date !='null' AND fact_deal_date !=''" +
            "    <if test=\"year !=null and year !=''\">\n" +
            "      and YEAR(fact_deal_date) = #{year}\n" +
            "    </if>\n" +
            "    <if test=\"month !=null and month !=''\">\n" +
            "      and MONTH(fact_deal_date) = #{month}\n" +
            "    </if>\n" +
            " GROUP BY send_goods_customer ORDER BY SUM(carriage+reduce_fund) DESC" +
            "</script>")
    List<MoneyFreight> selectAllCarriageRanking(String year,String month);

//    @Select("SELECT sum(carriage) AS carriage,sum(carry_goods_fee) AS carry_goods_fee,sum(insurance) AS insurance,fact_deal_date " +
//            "FROM goodsbill_master WHERE flag=0 GROUP BY MONTH(fact_deal_date) ORDER BY MONTH(fact_deal_date)")
    List<GoodsbillMaster> selectBusinessIncomeByMonth(@Param("years") String year,@Param("months") String month);

    /**
     * 根据货运单id查数据
     * @return
     */
    @Select("SELECT * FROM goodsbill_master WHERE goods_bill_code=#{goodsBillCode}")
    List<GoodsbillMaster> selectBillByBillCode(String goodsBillCode);


    @Select("<script>SELECT send_goods_customer AS name,sum(carriage) AS carriage,sum(reduce_fund) AS reduce_fund,sum(pay_kickback) AS pay_kickback," +
            "sum(money_of_change_pay) AS money_of_change_pay,sum(carry_goods_fee) AS carry_goods_fee,sum(insurance) AS insurance " +
            "FROM goodsbill_master WHERE flag=0  AND fact_deal_date !='null' AND fact_deal_date !=''" +
            "    <if test=\"year !=null and year !=''\">\n" +
            "      and YEAR(fact_deal_date) = #{year}\n" +
            "    </if>\n" +
            "    <if test=\"month !=null and month !=''\">\n" +
            "      and MONTH(fact_deal_date) = #{month}\n" +
            "    </if>\n" +
            "GROUP BY send_goods_customer ORDER BY carriage DESC</script>")
    List<MoneyFreight> selectCarriageRankingByDate(String year,String month);
}